1. Field of the Invention
This invention relates to a method of retrieving a database and, more particularly, to a method of preparing a database query statement.
A function of assembling a database query statement of a SQL (Structured Query Language), etc., of an RDB (Relational DataBase) utilizing a GUI (Graphical User Interface) is generally called a query builder and is implemented in many stand alone products and other products such as xe2x80x9cMS Access 97xe2x80x9d (Access is a trademark of Microsoft) and xe2x80x9cLotus Approach97xe2x80x9d (Approach is a trademark of Lotus).
Each of such products is so designed as to allow visual selection of a field and description of conditions by using a GUI as shown in FIG. 15 and FIG. 16. However, in order to prepare an SQL statement by joining a plurality of tables in these products, the only method available in the technology employed in these products is simply to display field name and join them. Thus, the user had to perform a joining operation by taking care of the data type of each field. Accordingly, there was a problem in that the user had to learn the data type of each field or repeat trail and error steps in performing the joining operation and was unable to work efficiently.
Especially, selection of the data type is not simple and there is a case where joining is permitted even if the data types are not necessarily of a same type. For example, joining of Float type and Date type is not permitted while joining of Float type and Real type is permitted.
A specific example of a typical query builder is described hereunder.
FIG. 15 is a chart showing the GUI of Microsoft Access97. The GUI shown in FIG. 15 is in a state in which a field of a first table (source table) 500 is selected and the mouse cursor is on a field of a second table (target table) 510 with the left button held depressed when two tables are to be joined. The user has to decide an item to be joined from the field name and affirm the attribute of a table by opening it when it is desired to know the data type. If the user fails to do this correctly, the SQL statement would cause an error upon execution so that an application could not be developed efficiently.
FIG. 16 is a chart showing the GUI of Lotus Approach97. The GUI shown FIG. 16 is also in a state in which a field of a first table (source table) 500 is selected and the mouse cursor is on a field of a second table (target table) 510 with the left button held depressed when two tables are to be joined. While the data type of the position where the mouse cursor is currently placed is given in the lower right field, it is impossible to visually determine which data type can be joined with the data type of the first table so that an SQL statement could not be prepared efficiently.
It is an object of this invention to allow a database query statement involving a joining of a plurality of tables to be prepared visually and efficiently without an error.
When the user selects a data item of the source data field and drags the mouse cursor to a list box of the target table, a field of the target table which can not be joined with the data type of the field of the initially selected source table is displayed in the display distinctively from a field with which it can be joined.
In one aspect of this invention, a method is provided for displaying a data item executed on a data item display device which comprises a display and a pointing device and acquires from a data source information of a source database table including a plurality of source data fields each having attribute information and a target database table including a plurality of target data fields each having attribute information to display in said display at least a portion of said plurality of source data fields and said plurality of target data fields as a plurality of source data items and a plurality of target data items, said method comprising the steps of:
(a) displaying a source list box including a plurality of source data items and a target list box including a plurality of target data items in said display;
(b) detecting that one of said source data items is dragged by said pointing device;
(c) determining whether or not the pointer of said dragged pointing device is on said target list box;
(d) determining whether or not the attribute information of the target data field corresponding to at least a part of said plurality of target data items matches a predetermined condition with respect to the attribute information of the source data field corresponding to said dragged source data item when the pointer is on said target list box; and
(e) displaying a target data item corresponding to the attribute information of the target data field which is determined not to match said predetermined condition distinctively from a target data item corresponding to the attribute information of the target data field which is determined to match said predetermined condition.
Incidentally, the term xe2x80x9cattribute informationxe2x80x9d as used in the claims of this specification is a concept including not only the data type to be described in the preferred embodiment of this invention but also various attribute information correlated to the fields.
Also, the term xe2x80x9cdistinctively displayxe2x80x9d as used in the claims of this specification is a concept including modification of various display attributes and display contents such as modification of color, brightness and font, and display with blinking, inversion display, underscoring and shading.
In another aspect of this invention, a method is provided for displaying a data item executed on a data item display device which comprises a display and a pointing device and acquires from a data source information of a source database table including a plurality of source data fields each having attribute information and a target database table including a plurality of target data fields each having attribute information to display in said display at least a portion of said plurality of source data fields and said plurality of target data fields as a plurality of source data items and a plurality of target data items, said method comprising the steps of:
(a) displaying a source list box including a plurality of source data items and a target list box including a plurality of target data items in said display;
(b) detecting that one of said source data items is specified by an operator;
(c) determining whether or not the attribute information of the target data field corresponding to at least a part of said plurality of target data items matches a predetermined condition with respect to the attribute information of the source data field corresponding to said specified source data item; and
(d) displaying a target data item corresponding to the attribute information of the target data field which is determined not to match said predetermined condition distinctively from a target data item corresponding to the attribute information of the target data field which is determined to match said predetermined condition.
In another aspect of this invention, a data item display device is provided for displaying a data item executed on a data item display device which comprises a display and a pointing device and acquires from a data source information of a source database table including a plurality of source data fields each having attribute information and a target database table including a plurality of target data fields each having attribute information to display in said display at least a portion of said plurality of source data fields and said plurality of target data fields as a plurality of source data items and a plurality of target data items, said data item display device comprising:
(a) a display for displaying a source list box including a plurality of source data items and a target list box including a plurality of target data items in said display;
(b) a target list box control part instructing to;
(b-1) determine whether or not the attribute information of the target data field corresponding to at least a part of said plurality of target data items matches a predetermined condition with respect to the attribute information of the source data field corresponding to one source data item specified by an operator among said plurality of source data items; and
(b-2) display a target data item corresponding to the attribute information of the target data field which is determined not to match said predetermined condition distinctively from a target data item corresponding to the attribute information of the target data field which is determined to match said predetermined condition.
In another aspect of this invention, a recording medium storing an image processing program is provided for controlling display of a data item executed on a data item display device which comprises a display and a pointing device and acquires from a data source information of a source database table including a plurality of source data fields each having attribute information and a target database table including a plurality of target data fields each having attribute information to display in said display at least a portion of said plurality of source data fields and said plurality of target data fields as a plurality of source data items and a plurality of target data items, said image processing program comprising:
(a) a program code to instruct said data item display device to display a source list box including a plurality of source data items and a target list box including a plurality of target data items in said display;
(b) a program code to instruct said data item display device to detect that one of said source data items is dragged by said pointing device;
(c) determining whether or not the pointer of said dragged pointing device is on said target list box;
(d) a program code to instruct said data item display device to determine whether or not the attribute information of the target data field corresponding to at least a part of said plurality of target data items matches a predetermined condition with respect to the attribute information of the source data field corresponding to said dragged source data item when the pointer is on said target list box; and
(e) a program code to instruct said data item display device to display a target data item corresponding to the attribute information of the target data field which is determined not to match said predetermined condition distinctively from a target data item corresponding to the attribute information of the target data field which is determined to match said predetermined condition.
In another aspect of this invention, a recording medium storing an image processing program is provided for controlling display of a data item executed on a data item display device which comprises a display and a pointing device and acquires from a data source information of a source database table including a plurality of source data fields each having attribute information and a target database table including a plurality of target data fields each having attribute information to display in said display at least a portion of said plurality of source data fields and said plurality of target data fields as a plurality of source data items and a plurality of target data items, said image processing program comprising:
(a) a program code to instruct said data item display device to display a source list box including a plurality of source data items and a target list box including a plurality of target data items in said display;
(b) a program code to instruct said data item display device to detect that one of said source data items is specified by an operator;
(c) a program code to instruct said data item display device to determine whether or not the attribute information of the target data field corresponding to at least a part of said plurality of target data items matches a predetermined condition with respect to the attribute information of the source data field corresponding to said specified source data item; and
(d) a program code to instruct said data item display device to display a target data item corresponding to the attribute information of the target data field which is determined not to match said predetermined condition distinctively from a target data item corresponding to the attribute information of the target data field which is determined to match said predetermined condition.